package com.fzg.common.tool;

import java.sql.*;

/**
 * create by fzg
 * 2022/8/5 13:49
 */


public class MySQLConnection {

    //驱动名称（MySQL 8.0之前版本的驱动为："com.mysql.jdbc.Driver"）
    private static final String Driver = "com.mysql.cj.jdbc.Driver";
    //连接地址:格式为：jdbc:mysql://localhost:端口号(默认为3306)/数据库名称
    private static final String Url = "jdbc:mysql://localhost:3306/mycaveolae";
    //用户名
    private static final String User = "root";
    //用户密码:这里是自己电脑上数据库的密码
    private static final String Password = "123456";


    public static void main(String[] args) {
        try {
            //1、加载驱动
            Class.forName(Driver);
            //判断是否加载成功
            System.out.print("加载驱动成功！");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("加载驱动失败！");
        }
        Connection con = null;
        try{
            //2、获取数据库连接
            con = DriverManager.getConnection(Url,User,Password);
            System.out.println("获取数据库连接成功！");
            System.out.println("进行数据库操作！");

            //执行sql查询语句
            // executeSelectSql(con);

            // 执行sql添加语句
            // addDemoMethod(con);

            // 更新语句
            // updateDemoMethod(con);

            // 删除语句
            deleteDemo(con);

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("获取数据库连接失败！");
        }
        finally {
            try{
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 执行sql查询语句
     * @param con 参数
     * @throws SQLException SQL异常
     */
    public static void executeSelectSql(Connection con) throws SQLException{
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from `tag`");
        System.out.println("select * from `tag` 查询结果如下：");
        //打印结果集
        while (resultSet.next()){
            System.out.print(resultSet.getString("aid") + " ");
            System.out.print(resultSet.getString("name") + " ");
            System.out.print(resultSet.getString("user_aid") + " ");
            System.out.println();
        }
    }

    /**
     * 执行sql添加语句
     * @param con 参数：连接对象
     * @throws SQLException SQL异常
     */
    public static void addDemoMethod(Connection con) throws SQLException{
        Statement statement = con.createStatement();
        System.out.println("insert into `tag`(`name`,user_aid) values ('恋爱脑',1)");
        int insert = statement.executeUpdate("insert into `tag`(`name`,user_aid) values ('恋爱脑',1)");
        if (insert > 0){
            System.out.println("插入成功！");
        }else {
            System.out.println("插入失败！");
        }
    }

    /**
     * 更新语句
     * @param con 参数：连接对象
     * @throws SQLException SQL异常
     */
    public static void updateDemoMethod(Connection con) throws SQLException{
        Statement statement = con.createStatement();
        System.out.println("update `tag` set user_aid = 2 where aid = 13 and name = '恋爱脑'");
        int update = statement.executeUpdate("update `tag` set user_aid = 2 where aid = 13 and name = '恋爱脑'");
        if (update > 0){
            System.out.println("更新成功！");
        }else {
            System.out.println("更新失败！");
        }
    }

    /**
     * 删除语句
     * @param con 参数：连接对象
     * @throws SQLException SQL异常
     */
    public static void deleteDemo(Connection con) throws SQLException{
        Statement statement = con.createStatement();
        System.out.println("delete from `tag` where aid = 9");
        int delete = statement.executeUpdate("delete from `tag` where aid = 9");
        if (delete > 0){
            System.out.println("删除成功！");
        }else {
            System.out.println("删除失败！");
        }
    }


}
